﻿CREATE TABLE [Mapping].[SourceKey]
(
	[Id] INT NOT NULL PRIMARY KEY NONCLUSTERED IDENTITY, 
	[MappingItem] INT NOT NULL,
  [KeyColumn] NVARCHAR(50) NOT NULL, 
  [KeyValue] NVARCHAR(20) NOT NULL, 
  CONSTRAINT [FK_SourceKey_MappingItem] FOREIGN KEY ([MappingItem]) REFERENCES [Mapping].[MappingItem]([Id]) ON DELETE CASCADE
)

GO

CREATE UNIQUE CLUSTERED INDEX [IX_SourceKey_Unique] ON [Mapping].[SourceKey] ([MappingItem], [KeyColumn])

GO

CREATE TRIGGER [Mapping].[Trigger_SourceKey]
  ON [Mapping].[SourceKey]
  AFTER DELETE, INSERT, UPDATE
  AS
  BEGIN
    SET NoCount ON

    DECLARE curr CURSOR FOR

    WITH Effected AS (
      SELECT DISTINCT MappingItem
        FROM deleted
      UNION SELECT DISTINCT MappingItem 
        FROM inserted
    )
    SELECT [Group] 
      FROM Mapping.MappingItem 
      WHERE [Id] IN (SELECT MappingItem FROM Effected)

    OPEN curr

    DECLARE @groupId int
    
    FETCH NEXT FROM curr INTO @groupId
    WHILE @@FETCH_STATUS = 0
    BEGIN
      EXEC Mapping.GenerateRuntimeView @groupId
      FETCH NEXT FROM curr INTO @groupId
    END

    CLOSE curr
    DEALLOCATE curr
  END